﻿using Dapper;
using Dapper.Contrib.Extensions;
using Gx.Common;
using Gx.Common.Core;
using Gx.Dal.Extensions;
using Gx.Dal.Shared;
using Gx.Model.Entity;
using Gx.Model.Enums;
using Gx.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Diagnostics.Contracts;
using System.Linq;
namespace Gx.Dal.Manage
{
    public class LKTransferDal : BaseDal<LKTransfer>
    {

        /// <summary>
        /// 名称是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AnyName(string name)
        {
            using (var conn = new DbContext())
            {
                var total = conn.Connection.QueryFirst<int>("SELECT COUNT(1) FROM LK_Transfer WITH(NOLOCK) WHERE IsDeleted=0 AND Name=@name", new { name });
                return total > 0;
            }
        }

        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxLKTransferCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>("SELECT MAX(CAST(SUBSTRING(TransferNo, 2,LEN(TransferNo)-1) AS int)) FROM LK_Transfer WITH(NOLOCK) WHERE ISNUMERIC(SUBSTRING(TransferNo, 2, LEN(TransferNo) - 1)) = 1");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedList<LKTransfer> GetLKTransferPageList(LKTransferInput input)
        {
            var ids = GetTransferIdList(input);
            string sql = "SELECT * FROM LK_Transfer WITH(NOLOCK) WHERE IsDeleted=0 @WHERE";
            List<string> conditions = new List<string>();
            //判断input的属性是否为空，如果不为空则添加到conditions中
            if (ids != null)
            {
                if (ids.Count == 0)
                {
                    ids.Add(0);
                }
                input.Ids = ids;
                conditions.Add("Id IN @Ids");
            }

            if (!string.IsNullOrWhiteSpace(input.BillNo))
            {
                input.BillNo = $"%{input.BillNo}%";
                conditions.Add("TransferNo LIKE @BillNo");
            }
            if (input.InCustomerId.HasValue)
            {
                conditions.Add("InCustomerId=@InCustomerId");
            }
            if (input.OutCustomerId.HasValue)
            {
                conditions.Add("OutCustomerId=@OutCustomerId");
            }

            if (input.BeginTime.HasValue)
            {
                conditions.Add("CreateTime>=@BeginTime");
            }
            if (input.EndTime.HasValue)
            {
                conditions.Add("CreateTime<=@EndTime");
            }

            if (input.ModifyUserId.HasValue)
            {
                conditions.Add("ModifyUserId=@ModifyUserId");
            }
            if (input.ModifyBeginTime.HasValue)
            {
                conditions.Add("ModifyTime>=@ModifyBeginTime");
            }
            if (input.ModifyEndTime.HasValue)
            {
                conditions.Add("ModifyTime<=@ModifyEndTime");
            }

            if (UserContext.RoleId != (int)Roles.管理员)
            {
                conditions.Add($"CompanyId={UserContext.CompanyId}");
            }


            string condition = string.Empty;
            if (conditions.Any())
            {
                condition = $" AND {string.Join(" AND ", conditions)}";
            }
            sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

            using (var conn = new DbContext())
            {
                return conn.Connection.GetPagedList<LKTransfer>(sql, "CreateTime Desc", input);
            }
        }

        /// <summary>
        /// 添加转卡单
        /// </summary>
        /// <param name="lKTransferDto"></param>
        /// <returns></returns>
        public bool AddLKTransfer(LKTransferDto lKTransferDto)
        {
            return UseTransaction((conn, trans) =>
            {
                var transfer = lKTransferDto.Transfer;
                var result = conn.Insert(transfer, trans);

                var details = lKTransferDto.Details;
                if (details != null && details.Any())
                {
                    var adds = details.Where(c => c.Id == 0).ToList();
                    if (adds.Any())
                    {
                        adds.ForEach(c => c.TransferId = transfer.Id);
                        conn.Insert(adds, trans);
                    }
                }

                var costs = lKTransferDto.Costs;
                if (costs != null && costs.Any())
                {
                    var adds = costs.Where(c => c.Id == 0).ToList();
                    if (adds.Any())
                    {
                        adds.ForEach(c => c.TransferId = transfer.Id);
                        conn.Insert(adds, trans);
                    }
                }

                return result > 0;
            });
        }

        /// <summary>
        /// 编辑转卡单
        /// </summary>
        /// <param name="lKTransferDto"></param>
        /// <returns></returns>
        public bool EditLKTransfer(LKTransferDto lKTransferDto)
        {
            return UseTransaction((conn, trans) =>
            {
                var transfer = lKTransferDto.Transfer;
                var result = conn.Update(transfer, trans);

                var details = lKTransferDto.Details;
                if (details != null && details.Any())
                {
                    var adds = details.Where(c => c.Id == 0).ToList();
                    var updates = details.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates, trans);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKTransferDetail>("TransferId=@transferId AND Id Not IN @ids", new { transferId = transfer.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKTransferDetail, int>(deletes.ToList(), trans);
                        }
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(c => c.TransferId = transfer.Id);
                        conn.Insert(adds, trans);
                    }
                }

                var costs = lKTransferDto.Costs;
                if (costs != null && costs.Any())
                {
                    var adds = costs.Where(c => c.Id == 0).ToList();
                    var updates = costs.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates, trans);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKTransferCost>("TransferId=@transferId AND Id Not IN @ids", new { transferId = transfer.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKTransferCost, int>(deletes.ToList(), trans);
                        }
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(c => c.TransferId = transfer.Id);
                        conn.Insert(adds, trans);
                    }
                }
                return result;
            });
        }

        //删除转卡单
        public bool DeleteLKTransfer(int id)
        {
            return UseTransaction((conn, trans) =>
            {
                var sql = "UPDATE LK_Transfer SET IsDeleted=1 WHERE Id=@id";
                var result = conn.Execute(sql, new { id }, trans);
                conn.Execute("UPDATE LK_TransferDetail SET IsDeleted=1 WHERE TransferId=@id", new { id }, trans);
                conn.Execute("UPDATE LK_TransferCost SET IsDeleted=1 WHERE TransferId=@id", new { id }, trans);

                return result > 0;
            });
        }


        private List<int> GetTransferIdList(LKTransferInput input)
        {


            using (var conn = new DbContext())
            {
                List<int> houseids = null;//new List<int>();
                if (input.WarehouseId.HasValue && input.WarehouseId != 0)
                {
                    houseids = new List<int>
                    {
                        input.WarehouseId.Value
                    };
                    string housesql = @"WITH RecursiveHouse AS (
    SELECT Id, ParentId
    FROM LK_WareHouse WITH(NOLOCK)
    WHERE ParentId = @WarehouseId  -- 这里的@ParentID是要查询子节点的起始节点ID
    UNION ALL
    SELECT o.Id, o.ParentId
    FROM LK_WareHouse AS o WITH(NOLOCK)
    INNER JOIN RecursiveHouse AS r ON o.ParentId = r.Id
)
SELECT Id FROM RecursiveHouse;";

                    var hids = conn.Connection.Query<int>(housesql, new { input.WarehouseId }).ToList();

                    if (hids != null && hids.Any())
                    {
                        houseids.AddRange(hids);
                    }
                }

                string sql = @"SELECT DISTINCT d.TransferId FROM dbo.LK_TransferDetail d WITH(NOLOCK)
LEFT JOIN dbo.LK_Goods g WITH(NOLOCK) ON d.GoodsId=g.Id AND g.IsDeleted=0
LEFT JOIN dbo.LK_GoodsSpec gs WITH(NOLOCK) ON d.GoodsSpecId=gs.Id AND gs.IsDeleted=0
WHERE d.IsDeleted=0  @WHERE";

                List<string> conditions = new List<string>();
                if (houseids != null)
                {
                    input.HouseIds = houseids;
                    conditions.Add("d.OutHouse IN @HouseIds");
                }
                if (!string.IsNullOrWhiteSpace(input.NewGoodsCode))
                {
                    input.NewGoodsCode = $"%{input.NewGoodsCode}%";
                    conditions.Add("d.NewGoodsNo LIKE @NewGoodsCode");
                }
                if (!string.IsNullOrWhiteSpace(input.GoodsName))
                {
                    input.GoodsName = $"%{input.GoodsName}%";
                    conditions.Add("g.GoodsName LIKE @GoodsName");
                }



                if (!conditions.Any()) return null;

                string condition = $" AND {string.Join(" AND ", conditions)}";

                sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

                return conn.Connection.Query<int>(sql, input).ToList();
            }
        }  
    }
}

